package com.ruoyi.business.JuheAdminController;

import com.alibaba.fastjson.JSONObject;
import com.ruoyi.business.utils.JuheRedisCache;
import com.ruoyi.common.core.redis.RedisCache;
import com.ruoyi.common.utils.DateUtils;
import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.system.domain.vo.R;
import com.ruoyi.system.protocol.IDynamicSearchService;
import com.ruoyi.system.protocol.impl.DynamicCacheServiceImpl;
import com.ruoyi.system.protocol.impl.DynamicSearchJuheServiceImpl;
import org.elasticsearch.index.query.BoolQueryBuilder;
import org.elasticsearch.index.query.QueryBuilders;
import org.elasticsearch.index.query.RangeQueryBuilder;
import org.elasticsearch.script.Script;
import org.elasticsearch.search.aggregations.AggregationBuilders;
import org.elasticsearch.search.aggregations.BucketOrder;
import org.elasticsearch.search.aggregations.bucket.histogram.DateHistogramAggregationBuilder;
import org.elasticsearch.search.aggregations.bucket.terms.TermsAggregationBuilder;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.util.Assert;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;

@RestController
@RequestMapping("juheAdmin")
//@RequestMapping("ownjuheAdminApi")
public class IndexJuheAdminController {


    @Autowired
    @Qualifier("juheJdbcTemplate39")
    private JdbcTemplate juheJdbcTemplate39;

    @Autowired
    @Qualifier("juheJdbcTemplate182")
    private JdbcTemplate juheJdbcTemplate182;

    @Autowired
    private JuheRedisCache juheRedisCache;

    @Autowired
    @Qualifier("dynamicSearchJuheServiceImpl")
    private IDynamicSearchService dynamicSearchJuheServiceImpl;

    final static private String INDEX = "juhe";

    @RequestMapping("/list")
    public R channelList(String type, String id, String name, String token, int pageNum, int pageSize) {

        Assert.notNull(pageNum, "pageNum不能为空");
        Assert.notNull(pageSize, "pageSize不能为空");

        List<Object> params = new ArrayList<>();
        List<Object> paramsCount = new ArrayList<>();
        String sql = "select * from channel where 1=1";
        String sqlCount = "select count(*) from channel where 1=1";
        if(StringUtils.isNotBlank(id)){
            sql += " AND id=?";
            sqlCount += " AND id=?";
            params.add(id);
        }
        if(StringUtils.isNotBlank(name)){
            sql += " AND (promote_products like concat('%',?,'%') or channel_name like concat('%',?,'%'))";
            sqlCount += " AND (promote_products like concat('%',?,'%') or channel_name like concat('%',?,'%'))";
            params.add(name);
            params.add(name);
        }
        if(StringUtils.isNotBlank(token)){
            sql += " AND (send_url like concat('%',?,'%') or link_name like concat('%',?,'%'))";
            sqlCount += " AND (send_url like concat('%',?,'%') or link_name like concat('%',?,'%'))";
            params.add(token);
            params.add(token);
        }

        paramsCount.addAll(params);

        sql += " order by id desc limit ?,?";
        params.add((pageNum - 1) * pageSize);
        params.add(pageSize);

        List<Map<String, Object>> list = new ArrayList<>();
        int count = 0;
        if("39".equals(type)){
            list = juheJdbcTemplate39.queryForList(sql, params.toArray(new Object[]{}));
            count = juheJdbcTemplate39.queryForObject(sqlCount, paramsCount.toArray(new Object[]{}), Integer.class);
        }else{
            list = juheJdbcTemplate182.queryForList(sql, params.toArray(new Object[]{}));
            count = juheJdbcTemplate182.queryForObject(sqlCount, paramsCount.toArray(new Object[]{}), Integer.class);
        }
        return R.success().data("data", list).data("count", count);
    }

    @RequestMapping("/findById")
    public R findById(String type, String id) {

        Assert.hasLength(id, "id不能为空");

        String url = "select * from channel where id=?";
        Map<String, Object> item = new HashMap<>();
        if("39".equals(type)){
            item = juheJdbcTemplate39.queryForMap(url, new Object[]{id});
        }else{
            item = juheJdbcTemplate182.queryForMap(url, new Object[]{id});
        }

        Map<String, Object> res = new HashMap<>();
        //item转为驼峰命名
        for (String key : item.keySet()) {
            Object value = item.get(key);
            String newKey = StringUtils.toCamelCase(key);
            res.put(newKey, value);
        }
        return R.success().data("data", res);
    }

    @RequestMapping("/add/{type}")
    public R add(@PathVariable("type") String type, @RequestBody JSONObject params) {

        String url = "INSERT INTO channel (%s) VALUES (%s)";
        List<String> fieldList = new ArrayList<>();
        List<String> valueList = new ArrayList<>();
        List<String> paramsList = new ArrayList<>();


        //id自增 在这里不需要
        params.remove("id");


        params.put("create_time", DateUtils.getTime());
        for (String key : params.keySet()) {
            fieldList.add("`"+StringUtils.toUnderScoreCase(key)+"`");
            valueList.add("?");
            paramsList.add(params.getString(key));
        }

        String sql = String.format(url, String.join(",", fieldList), String.join(",", valueList));
        int res = 0;
        KeyHolder keyHolder = new GeneratedKeyHolder();


        PreparedStatementCreator psc = conn -> {
            PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            for (int i = 0; i < paramsList.size(); i++) {
                ps.setObject(i + 1, paramsList.get(i));
            }
            return ps;
        };

        if("39".equals(type)){
            res = juheJdbcTemplate39.update(psc, keyHolder);
        }else {
            res = juheJdbcTemplate182.update(psc, keyHolder);
        }
        int id = keyHolder.getKey().intValue();
        return R.success().data("data", res).data("id", id);
    }

    @RequestMapping("/edit/{type}")
    public R edit(@PathVariable("type") String type, @RequestBody JSONObject params) {

        String id = params.get("id") + "";
        Assert.hasLength(id, "id不能为空");

        String url = "UPDATE channel SET %s WHERE id=?";
        List<String> fieldList = new ArrayList<>();
        List<String> paramsList = new ArrayList<>();

        //id自增 在这里不需要
        params.remove("id");
        params.put("create_time", DateUtils.getTime());
        for (String key : params.keySet()) {
            fieldList.add("`"+StringUtils.toUnderScoreCase(key)+"`=?");
            paramsList.add(params.getString(key));
        }
        paramsList.add(id);
        String sql = String.format(url, String.join(",", fieldList));
        int res = 0;
        if("39".equals(type)){
            res = juheJdbcTemplate39.update(sql, paramsList.toArray(new Object[]{}));
        }else {
            res = juheJdbcTemplate182.update(sql, paramsList.toArray(new Object[]{}));
        }
        return R.success().data("data", res);
    }


    @RequestMapping("/refreshCacheAll/{type}")
    public R refreshCacheAll(@PathVariable("type") String type) {

        String url = "select * from channel";
        List<Map<String, Object>> mapList = new ArrayList<>();
        if("39".equals(type)){
            mapList = juheJdbcTemplate39.queryForList(url);
        }else {
            mapList = juheJdbcTemplate182.queryForList(url);
        }

        for (Map<String, Object> item : mapList) {
            String id = item.get("id") + "";
            Map<String, Object> res = new HashMap<>();
            for (String key : item.keySet()) {
                Object value = item.get(key);
                String newKey = StringUtils.toCamelCase(key);
                res.put(newKey, value);
            }
            juheRedisCache.setCacheMapValue("channelList"+type, "channelId_"+id, res);
        }


        return R.success();
    }

    @RequestMapping("/refreshCacheSingle/{type}")
    public R refreshCacheSingle(@PathVariable("type") String type, String id) {

        Assert.hasLength(id, "id不能为空");

        String url = "select * from channel where id=?";
        Map<String, Object> item = new HashMap<>();
        if("39".equals(type)){
            item = juheJdbcTemplate39.queryForMap(url, new Object[]{id});
        }else{
            item = juheJdbcTemplate182.queryForMap(url, new Object[]{id});
        }

        Map<String, Object> res = new HashMap<>();
        for (String key : item.keySet()) {
            Object value = item.get(key);
            String newKey = StringUtils.toCamelCase(key);
            res.put(newKey, value);
        }
        juheRedisCache.setCacheMapValue("channelList"+type, "channelId_"+id, res);
        return R.success();
    }


    @RequestMapping("/clickOnTheSplitFace")
    public R clickOnTheSplitFace(String id) {
        Assert.hasLength(id, "id不能为空");


        List<String> dateList = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            dateList.add(0, DateUtils.getOffsetDate(i * -1).substring(0, 10));
        }
        BoolQueryBuilder boolQueryBuilder = new BoolQueryBuilder();
        boolQueryBuilder.must(QueryBuilders.termQuery("channelId.keyword", id));
        boolQueryBuilder.must(QueryBuilders.termQuery("type.keyword", "click"));
        boolQueryBuilder.must(new RangeQueryBuilder("createTime.keyword").gte(dateList.get(0)+" 00:00:00").lte(dateList.get(dateList.size()-1)+" 23:59:59").format("yyyy-MM-dd HH:mm:ss"));
        String idOrCode = "doc['createTime.keyword'].value.substring(0,10)";
        Script script = new Script(Script.DEFAULT_SCRIPT_TYPE, Script.DEFAULT_SCRIPT_LANG, idOrCode, Collections.emptyMap());
        TermsAggregationBuilder dimensionAgg = AggregationBuilders.terms("ymdFacet")
                .script(script).order(BucketOrder.key(false))
                .size(10);
        String statusCode = "doc['responseStr.keyword'].value.contains('上报点击发送成功')? '成功' : '失败'";
        Script statusScipt = new Script(Script.DEFAULT_SCRIPT_TYPE, Script.DEFAULT_SCRIPT_LANG, statusCode, Collections.emptyMap());
        TermsAggregationBuilder statusAgg = AggregationBuilders.terms("statusFacet")
                .script(statusScipt);
        dimensionAgg.subAggregation(statusAgg);
        Map<String, Object> map = dynamicSearchJuheServiceImpl.analysisQuery(INDEX, boolQueryBuilder, dimensionAgg);


        boolQueryBuilder = new BoolQueryBuilder();
        boolQueryBuilder.must(QueryBuilders.termQuery("channelId.keyword", id));
        boolQueryBuilder.must(QueryBuilders.termQuery("type.keyword", "click"));
        boolQueryBuilder.must(new RangeQueryBuilder("createTime.keyword").gte(dateList.get(0)+" 00:00:00").lte(dateList.get(dateList.size()-1)+" 23:59:59").format("yyyy-MM-dd HH:mm:ss"));

        TermsAggregationBuilder dimensionAgg2 = AggregationBuilders.terms("ymdFacet")
                .script(script).order(BucketOrder.key(false))
                .size(10);
        TermsAggregationBuilder typeAgg = AggregationBuilders.terms("type").field("type.keyword");
        dimensionAgg2.subAggregation(typeAgg);
        Map<String, Object> map2 = dynamicSearchJuheServiceImpl.analysisQuery(INDEX, boolQueryBuilder, dimensionAgg2);

        List<Map<String, Object>> resList = new ArrayList<>();
        Map<String, Object> res = new HashMap<>();
        for (String date : dateList) {
            res = new HashMap<>();
            Object clickTotal = 0;
            Object callbackTotal = 0;
            try{
                Map<String, Object> ymdAgg = (Map<String, Object>)map2.get("ymdFacet");
                Map<String, Object> dateAgg = (Map<String, Object>)ymdAgg.get(date);
                Map<String, Object> typeAggTemp = (Map<String, Object>)dateAgg.get("type");
                clickTotal = typeAggTemp.getOrDefault("click", 0);
                callbackTotal = typeAggTemp.getOrDefault("callback", 0);
            }catch (Exception e){
                System.out.println(e.getMessage());
            }

            Object clickSuccess = 0;
            Object clickError = 0;
            try{
                Map<String, Object> ymdAgg2 = (Map<String, Object>)map.get("ymdFacet");
                Map<String, Object> dateAgg2 = (Map<String, Object>)ymdAgg2.get(date);
                Map<String, Object> statusFacet = (Map<String, Object>)dateAgg2.get("statusFacet");
                clickSuccess = statusFacet.getOrDefault("成功", 0);
                clickError = statusFacet.getOrDefault("失败", 0);
            }catch (Exception e){
                System.out.println(e.getMessage());
            }

            res.put("date", date);
            res.put("clickTotal", clickTotal);
            res.put("callbackTotal", callbackTotal);
            res.put("clickSuccess", clickSuccess);
            res.put("clickError", clickError);
            resList.add(res);
        }
        //resList 反转
        Collections.reverse(resList);
        return R.success().data("list", resList );
    }

    @RequestMapping("/getTodaySReport")
    public R getTodaySReport(String date) {

        if(StringUtils.isBlank(date)) {
            date = DateUtils.getDate();
        }
        BoolQueryBuilder boolQueryBuilder = new BoolQueryBuilder();
        boolQueryBuilder.must(QueryBuilders.wildcardQuery("createTime.keyword", date+"*"));
        boolQueryBuilder.must(QueryBuilders.termQuery("type.keyword", "click"));
        Long clickTotal = dynamicSearchJuheServiceImpl.searchCount(INDEX, null, boolQueryBuilder);

        boolQueryBuilder = new BoolQueryBuilder();
        boolQueryBuilder.must(QueryBuilders.wildcardQuery("createTime.keyword", date+"*"));
        boolQueryBuilder.must(QueryBuilders.termQuery("type.keyword", "click"));
        boolQueryBuilder.must(QueryBuilders.wildcardQuery("responseStr.keyword", "*上报点击发送成功*"));
        Long successTotal = dynamicSearchJuheServiceImpl.searchCount(INDEX, null, boolQueryBuilder);

        boolQueryBuilder = new BoolQueryBuilder();
        boolQueryBuilder.must(QueryBuilders.wildcardQuery("createTime.keyword", date+"*"));
        boolQueryBuilder.must(QueryBuilders.termQuery("type.keyword", "click"));
        boolQueryBuilder.mustNot(QueryBuilders.wildcardQuery("responseStr.keyword", "*上报点击发送成功*"));
        Long errorTotal = dynamicSearchJuheServiceImpl.searchCount(INDEX, null, boolQueryBuilder);

        boolQueryBuilder = new BoolQueryBuilder();
        boolQueryBuilder.must(QueryBuilders.wildcardQuery("createTime.keyword", date+"*"));
        boolQueryBuilder.must(QueryBuilders.termQuery("type.keyword", "callback"));
        Long callbackTotal = dynamicSearchJuheServiceImpl.searchCount(INDEX, null, boolQueryBuilder);

        return R.success()
                .data("date", date)
                .data("clickTotal", clickTotal)
                .data("successTotal", successTotal)
                .data("errorTotal", errorTotal)
                .data("callbackTotal", callbackTotal);

    }

}
